# -*- coding: utf-8 -*-
#
#   Copyright 2009 Andrew Wilkinson <andrewjwilkinson@gmail.com>
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.

from pyparsing import *

# define SQL tokens
selectStmt = Forward()
selectToken = Keyword("select", caseless=True)
fromToken   = Keyword("from", caseless=True)
useToken    = Keyword("use", caseless=True)
createToken = Keyword("create database", caseless=True)
dropToken   = Keyword("drop database", caseless=True)

ident          = Word( alphas, alphanums + "_$" ).setName("identifier")
columnName     = ident
columnNameList = Group( delimitedList( columnName ) )
tableName      = ident | Literal("_")
tableNameList  = Group( delimitedList( tableName ) )

whereExpression = Forward()
and_ = Keyword("and", caseless=True)
or_ = Keyword("or", caseless=True)
in_ = Keyword("in", caseless=True)

E = CaselessLiteral("E")
binop = oneOf("= != < > >= <= eq ne lt le gt ge", caseless=True)
arithSign = Word("+-",exact=1)
realNum = Combine( Optional(arithSign) + ( Word( nums ) + "." + Optional( Word(nums) )  |
                                                         ( "." + Word(nums) ) ) +
            Optional( E + Optional(arithSign) + Word(nums) ) )
intNum = Combine( Optional(arithSign) + Word( nums ) +
            Optional( E + Optional("+") + Word(nums) ) )

columnRval = realNum | intNum | quotedString | columnName | CaselessLiteral("%s")
whereCondition = Group(
    ( columnName + binop + columnRval ) |
    ( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
    ( columnName + in_ + "(" + selectStmt + ")" ) |
    ( "(" + whereExpression + ")" )
    )
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression )

# define the grammar
selectStmt      << ( selectToken +
                   ( '*' | columnNameList ).setResultsName( "columns" ) +
                   Suppress(fromToken) +
                   tableNameList.setResultsName( "tables" ) +
                   Optional( Group( Suppress(CaselessLiteral("where")) + whereExpression ), "" ).setResultsName("where") )

# define use queries

useStmt = useToken + ident.setResultsName("database")

# define create database queries

createStmt = createToken + ident.setResultsName("database")

# define drop database queries

dropStmt = dropToken + ident.setResultsName("database")

sql = selectStmt | useStmt | createStmt | dropStmt

parse_sql = sql.parseString
